﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace AspDotNet.Framework.CommonLibs
{
    public class SqlHelper
    {
        private SqlConnection sqlConnection;
        private SqlCommand sqlCommand;
        private string connectionStr = "data source=.;initial catalog=Northwind;uid=kolalo;pwd=123456*Aa";

        #region 构造方法

        public SqlHelper(string connectionStr = null)
        {
            if (!string.IsNullOrWhiteSpace(connectionStr))
            {
                this.connectionStr = connectionStr;
            }
            sqlConnection = new SqlConnection(connectionStr);
            sqlCommand = new SqlCommand();
            sqlCommand.Connection = sqlConnection;
        }
        #endregion

        #region 打开或断开数据库连接

        /// <summary>
        /// 打开数据库
        /// </summary>
        private void Open()
        {
            if (sqlConnection.State == ConnectionState.Closed)
            {
                sqlConnection.Open();
            }
        }

        /// <summary>
        /// 关闭数据库
        /// </summary>
        private void Close()
        {
            if (sqlConnection.State == ConnectionState.Open)
            {
                sqlConnection.Close();
            }
        }
        #endregion

        #region 初始化数据库命令
        private void InitializeSqlCommand()
        {
            sqlCommand.Connection = sqlConnection;
            sqlCommand.CommandText = "";
            sqlCommand.CommandType = CommandType.Text;
            sqlCommand.Parameters.Clear();
        }

        #endregion

        #region 获取数据集
        /// <summary>
        /// 获取数据集,通过SqlDataAdapter填充的方式
        /// </summary>
        /// <param name="commandText"></param>
        /// <param name="type"></param>
        /// <param name="sqlParameters"></param>
        /// <returns></returns>
        public DataSet GetDataSetBySqlDataAdapter(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            try
            {
                DataSet dataSet = new DataSet();
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandText, sqlConnection))
                {
                    sqlDataAdapter.SelectCommand.CommandType = type;

                    if (sqlParameters != null)
                    {
                        sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
                    }
                    sqlDataAdapter.Fill(dataSet);
                }
                return dataSet;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }
        #endregion

        #region 获取数据表

        /// <summary>
        /// http://www.debugease.com/aspdotnet/141656.html
        /// 获取数据库中某张表的数据,通过SqlDataReader的方式
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程</param>
        /// <param name="type">命令类型</param>
        /// <param name="sqlParameters">参数数组</param>
        /// <returns></returns>
        public DataTable GetDataTableBySqlDataReader(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                sqlCommand.CommandType = type;
                if (sqlParameters != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters);
                }
                Open();
                var reader = sqlCommand.ExecuteReader();
                var dataTable = new DataTable();
                dataTable.Load(reader);
                return dataTable;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }

        /// <summary>
        /// 获取数据库中某张表的数据,通过SqlDataAdapter填充的方式
        /// </summary>
        /// <param name="commandText">SQL语句或存储过程</param>
        /// <param name="type">命令类型</param>
        /// <param name="sqlParameters">参数数组</param>
        /// <returns></returns>
        public DataTable GetDataTableBySqlDataAdapter(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            try
            {
                DataTable dataTable = new DataTable();
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandText, sqlConnection))
                {
                    sqlDataAdapter.SelectCommand.CommandType = type;

                    if (sqlParameters != null)
                    {
                        sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
                    }
                    sqlDataAdapter.Fill(dataTable);
                }
                return dataTable;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }
        #endregion

        #region 获取数据行
        public DataRow GetDataRow(string commandText)
        {
            DataTable dataTable = new DataTable();
            DataRow dataRow;
            try
            {
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandText, sqlConnection))
                {
                    Open();
                    sqlDataAdapter.Fill(dataTable);
                    if (dataTable.Rows.Count < 1)
                    {
                        return null;
                    }
                    dataRow = dataTable.Rows[0];
                }
                return dataRow;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }

        public DataRow GetDataRow(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            DataTable dataTable = new DataTable();
            DataRow dataRow;
            try
            {
                using (SqlDataAdapter sqlDataAdapter = new SqlDataAdapter(commandText, sqlConnection))
                {
                    if (sqlParameters != null)
                    {
                        sqlDataAdapter.SelectCommand.Parameters.AddRange(sqlParameters);
                    }
                    sqlDataAdapter.SelectCommand.CommandType = type;
                    Open();
                    sqlDataAdapter.Fill(dataTable);
                    if (dataTable.Rows.Count < 1)
                    {
                        return null;
                    }
                    dataRow = dataTable.Rows[0];
                }
                return dataRow;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }
        #endregion

        #region ExecuteNonQuery

        public int ExecuteNonQuery(string commandText)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                Open();
                return sqlCommand.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }

        public int ExecuteNonQuery(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            /**********************************************该段代码中有一个编译错误,但未找到**********************************************/
            //try
            //{
            //    int result;
            //    sqlCommand.CommandText = commandText;
            //    sqlCommand.CommandType = type;
            //    if (sqlParameters != null)
            //    {
            //        sqlCommand.Parameters.AddRange(sqlParameters);
            //    }
            //    Open();
            //    result = sqlCommand.ExecuteNonQuery();
            //    return result;
            //}
            //catch (Exception w)
            //{

            //}
            //finally
            //{
            //    Close();
            //    InitializeSqlCommand();
            //}
            /**********************************************该段代码中有一个编译错误,但未找到**********************************************/

            try
            {
                sqlCommand.CommandText = commandText;
                sqlCommand.CommandType = type;
                if (sqlParameters != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters);
                }
                Open();
                return sqlCommand.ExecuteNonQuery(); ;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }
        #endregion

        #region ExecuteScalar

        public object ExecuteScalar(string commandText)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                Open();
                return sqlCommand.ExecuteScalar();
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }

        public object ExecuteScalar(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                sqlCommand.CommandType = type;
                if (sqlParameters != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters);
                }
                Open();
                var s = sqlCommand.ExecuteScalar();
                return s;
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                Close();
                InitializeSqlCommand();
            }
        }
        #endregion

        #region ExecuteReader

        public SqlDataReader ExecuteReader(string commandText)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                Open();
                return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                //Close();
                InitializeSqlCommand();
            }
        }

        public SqlDataReader ExecuteReader(string commandText, SqlParameter[] sqlParameters = null, CommandType type = CommandType.Text)
        {
            try
            {
                sqlCommand.CommandText = commandText;
                sqlCommand.CommandType = type;
                if (sqlParameters != null)
                {
                    sqlCommand.Parameters.AddRange(sqlParameters);
                }
                Open();

                return sqlCommand.ExecuteReader(CommandBehavior.CloseConnection);
            }
            catch (Exception e)
            {
                throw new Exception(e.Message, e);
            }
            finally
            {
                //Close();
                InitializeSqlCommand();
            }
        }
        #endregion
    }
}